﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Card.Entity;
using System.Data.SqlClient;
using Card.DataBase;

namespace Card.DAL
{
    public class SITE_MANAGER : DataBase.BaseDAL
    {
        #region  成员方法

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int userID, string areaCode, Security.Passport passport)
        {
            string cmdText = @"SELECT COUNT(1) FROM SITE_MANAGER
											WHERE USER_ID=@USER_ID AND  SITE_CODE=@SITE_CODE ";
            SqlParameter[] cmdParms = new SqlParameter[2];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@USER_ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = userID;

            i++;
            cmdParms[i] = new SqlParameter("@SITE_CODE", SqlDbType.NVarChar, 50);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = areaCode;

            object obj = SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, cmdText, cmdParms);
            int result = 0;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                result = 0;
            }
            else
            {
                result = int.Parse(obj.ToString());
            }
            return result == 0 ? false : true;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public virtual DataSet GetList(string strWhere,Security.Passport passport)
        {
            string cmdText = @"SELECT 
											USER_ID,SITE_CODE
										 FROM SITE_MANAGER ";
            if (strWhere.Trim() != "")
            {
                cmdText += " WHERE " + strWhere;
            }
            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText);
        }


        /// <summary>
        /// 新增一条数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual bool Add(SiteManager entity,Security.Passport passport)
        {
            string cmdText = @"INSERT INTO SITE_MANAGER(
														USER_ID,
														SITE_CODE)
											VALUES(
														@USER_ID,
														@SITE_CODE)";
            SqlParameter[] cmdParms = new SqlParameter[2];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@USER_ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.UserID;

            i++;
            cmdParms[i] = new SqlParameter("@SITE_CODE", SqlDbType.NVarChar, 50);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = entity.SiteCode;

            int rowEffected = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, cmdText, cmdParms);
            return rowEffected == 1 ? true : false;
        }     

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public virtual SiteManager GetEntity(int userID,Security.Passport passport)
        {
            string cmdText = @"SELECT USER_ID,SITE_CODE FROM SITE_MANAGER
											WHERE USER_ID=@USER_ID ";
            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@USER_ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = userID;

            SiteManager entity = new Card.Entity.SiteManager();
            DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText, cmdParms);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["USER_ID"].ToString() != "")
                {
                    entity.UserID = int.Parse(ds.Tables[0].Rows[0]["USER_ID"].ToString());
                }
                entity.SiteCode = ds.Tables[0].Rows[0]["SITE_CODE"].ToString().Trim();
                return entity;
            }
            else
            {
                return null;
            }
        }



        /// <summary>
        /// 删除一条数据
        /// </summary>
        public virtual bool Delete(int userID, string cityCode, Security.Passport passport)
        {
            string cmdText = @"DELETE SITE_MANAGER
											WHERE USER_ID=@USER_ID AND SITE_CODE=@SITE_CODE ";
            SqlParameter[] cmdParms = new SqlParameter[2];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@USER_ID", SqlDbType.Int, 4);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = userID;

            i++;
            cmdParms[i] = new SqlParameter("@SITE_CODE", SqlDbType.NVarChar, 50);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = cityCode;

            int rowEffected = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, cmdText, cmdParms);
            return rowEffected == 1 ? true : false;
        }

        #endregion  成员方法

        #region 扩展
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetManagerByAreaCode(string cityCode, Security.Passport passport)
        {
            string cmdText = @"SELECT A.USER_ID,A.SITE_CODE,B.EMPLOYEE_NUMBER,B.USER_NAME,B.REAL_NAME
FROM SITE_MANAGER A,SYS_USERS B
 WHERE A.USER_ID=B.USER_ID AND A.SITE_CODE=@SITE_CODE 
";
            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@SITE_CODE", SqlDbType.NVarChar, 50);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = cityCode;

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText,cmdParms).Tables[0];
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetAreaByManager(int userID, Security.Passport passport)
        {
            string cmdText = @"SELECT A.USER_ID,A.SITE_CODE,B.NAME
FROM SITE_MANAGER A,AREA B
 WHERE USER_ID=@USER_ID AND A.SITE_CODE=B.SITE_CODE
";
            SqlParameter[] cmdParms = new SqlParameter[1];
            int i = -1;

            i++;
            cmdParms[i] = new SqlParameter("@USER_ID", SqlDbType.Int);
            cmdParms[i].Direction = ParameterDirection.Input;
            cmdParms[i].Value = userID;

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, cmdText, cmdParms).Tables[0];
        }
        #endregion
    }
}
